package com.apes.fn.scm.purchaseReturn.repository;

import com.apes.fn.scm.purchaseReturn.model.PurchaseAfter;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

/**
 * @BelongsProject: apes-server
 * @BelongsPackage: com.apes.fn.scm.purchaseReturn.repository
 * @ClassName: PurchaseAfterItemRepository
 * @Author: ljp
 * @CreateTime: 2020-08-04 14:11
 * @Description:
 * @Version: 1.0
 */
public interface PurchaseAfterRepository extends CustomRepository<PurchaseAfter, String> {
    @Query(value = "select * from (SELECT a.CREATE_DATE, b.inventory_id, b.inventory_supplier_id\n" +
            "                FROM acc_voucher a, acc_voucher_inventory b, SP_SANTOU_CHANNEL c\n" +
            "               WHERE a.id = b.voucher_id\n" +
            "                 AND b.inventory_supplier_id = c.SUPPLIER_ID\n" +
            "                 AND a.voucher_type IN ('CUSTOMER_SALE', 'CUSTOMER_CONSUME')\n" +
            "                 AND a.is_return = 0\n" +
            "                 AND b.product_qty - b.qty_returned > 0\n" +
            "                 AND a.company_id = :companyId\n" +
            "                 AND a.partner_id = :supplierId\n" +
            "                 AND b.product_id = :productId\n" +
            "              UNION all\n" +
            "              SELECT a.CREATE_DATE, b.id inventory_id,\n" +
            "                     b.SUPPLIER_ID inventory_supplier_id\n" +
            "                FROM acc_inventory_consign a, acc_inventory_batch b, SP_SANTOU_CHANNEL c\n" +
            "               WHERE a.inventory_batch_id = b.id\n" +
            "                 AND b.SUPPLIER_ID = c.SUPPLIER_ID\n" +
            "                 AND a.balance_qty - a.frost_balance_qty > 0\n" +
            "                 AND a.COMPANY_ID = :companyId\n" +
            "                 AND a.CUSTOMER_ID = :supplierId\n" +
            "                 AND a.product_id = :productId) a\n" +
            " order by a.CREATE_DATE desc",nativeQuery = true)
    List<Map> findOriginalInventorybach(@Param("companyId")String companyId,@Param("supplierId")String supplierId,@Param("productId")String productId);
}
